version 17.0
clear all
set more off
capture log close
cd "P:\2018\186\"

*1) Preparing the multi-generation register
use "P:\2018\186\Leverans\flergen_2017", replace
duplicates tag dnr2018186, g(t1)
keep if t1==0
drop t1
destring fodelsear fodelsearmor fodelsearfar, replace
rename (dnr2018186 dnr2018186m dnr2018186f fodelsear fodelsearmor fodelsearfar barn_flandgrp moder_flandgrp /*
*/fader_flandgrp kon) (idnr midnr fidnr yob myob fyob bcountry mbcountry fbcountry woman)
replace woman=0 if woman==1
replace woman=1 if woman==2
replace bcountry=1 if bcountry==.
replace fbcountry=1 if fbcountry==.
replace mbcountry=1 if mbcountry==.
lab var idnr "identity number"
lab var midnr "mother's identity number"
lab var fidnr "father's identity number"
lab var yob "year of birth"
lab var myob "mother's year of birth"
lab var fyob "father's year of birth"
lab var bcountry "birth country"
lab var mbcountry "mother's birth country"
lab var fbcountry "father's birth country"
compress
sort idnr
save "use\multigen", replace

*2) Creating a year of birth dataset
use idnr yob using "use\multigen", replace
save "temp\tempc", replace
local vars "f m"
foreach x of local vars{
	use `x'idnr `x'yob using "use\multigen", replace
	duplicates drop `x'idnr, force
	rename (`x'idnr `x'yob) (idnr yob)
	sort idnr
	save "temp\temp`x'", replace
}
use "temp\tempc", replace
append using "temp\tempf"
append using "temp\tempm"
duplicates drop idnr, force
compress
sort idnr
save "use\yob", replace
********************************************************************************
*2) Preparing birth record information
use "Leverans\fodelseuppgifter_2009", replace
duplicates tag dnr2018186, g(tag)
drop if tag!=0
drop tag
destring ateranvpnr kon flandgrp fodelselan, replace
rename (dnr2018186 kon flandgrp fodelselan) (idnr woman bcountry bcounty)
replace woman=0 if woman==1
replace woman=1 if woman==2
lab var idnr "identity number"
lab var bcountry "country of birth"
lab var bcounty "county of birth"
lab var woman "1 if woman"
compress
sort idnr
save "use\birthrecords", replace
*2014
use "Leverans\Fodelseuppgifter_2014", replace
duplicates tag dnr2018186, g(tag)
drop if tag!=0
drop tag
destring fodelselan, replace
rename (dnr2018186 kon flandgrp fodelselan) (idnr woman bcountry bcounty)
replace bcountry=1 if (bcounty>=1 | bcounty<=25) & bcountry==.
replace woman=0 if woman==1
replace woman=1 if woman==2
lab var idnr "identity number"
lab var bcountry "country of birth"
lab var bcounty "county of birth"
lab var woman "1 if woman"
merge 1:1 idnr using "use\birthrecords", keepusing(ateranvpnr bcountry bcounty woman) nogen
compress
sort idnr
save "use\birthrecords", replace
*2016
use "Leverans\Fodelseuppgifter_2016", replace
duplicates tag dnr2018186, g(tag)
drop if tag!=0
drop tag
destring flandgrp fodelselan, replace
rename (dnr2018186 kon flandgrp fodelselan) (idnr woman bcountry bcounty)
replace bcountry=1 if (bcounty>=1 | bcounty<=25) & bcountry==.
replace woman=0 if woman==1
replace woman=1 if woman==2
lab var idnr "identity number"
lab var bcountry "country of birth"
lab var bcounty "county of birth"
lab var woman "1 if woman"
merge 1:1 idnr using "use\birthrecords", keepusing(ateranvpnr bcountry bcounty woman) nogen
compress
sort idnr
save "use\birthrecords", replace
*2017
use "Leverans\Fodelseuppgifter_2017", replace
duplicates tag dnr2018186, g(tag)
drop if tag!=0
drop tag
destring flandgrp fodelselan, replace ignore("SE")
rename (dnr2018186 kon flandgrp fodelselan) (idnr woman bcountry bcounty)
replace bcountry=1 if (bcounty>=1 | bcounty<=25) & bcountry==.
replace woman=0 if woman==1
replace woman=1 if woman==2
lab var idnr "identity number"
lab var bcountry "country of birth"
lab var bcounty "county of birth"
lab var woman "1 if woman"
sort idnr
merge 1:1 idnr using "use\birthrecords", keepusing(ateranvpnr bcountry bcounty woman) nogen
drop if ateranvpnr==1
drop ateranvpnr
sort idnr
merge 1:1 idnr using "use\yob", keepusing(yob) keep(1 3)
compress
sort idnr
save "use\birthrecords", replace
********************************************************************************
*3) Preparing the FoB's
*1960 - 1965
forval i=60(5)65{
use "Leverans\Fob`i'", replace
	duplicates tag dnr2018186, g(duplicate)
	drop if duplicate!=0
	destring lan kommun bffodar, replace
	rename (dnr2018186 lan kommun bffodar)  (idnr county mun yob)
	lab var idnr "identity number"
	lab var county "county"
	lab var mun "municipality"
	lab var yob "year of birth"
	keep idnr county mun yob yrke syss yrkestall
	compress
	sort idnr
	save "use\fob19`i'", replace
}
*1970-1975
forval i=70(5)75{
	use "Leverans\fob`i'", replace
	duplicates tag dnr2018186, g(duplicate)
	drop if duplicate!=0
	destring lan kommun, replace
	rename (dnr2018186 lan kommun)  (idnr county mun)
	g earn19`i'=arbink *100		
	lab var idnr "identity number"
	lab var county "county"
	lab var mun "municipality"
	lab var earn19`i' "earnings"	
	keep idnr earn19`i' county mun yrke syss yrkestall
	compress
	sort idnr
	save "use\fob19`i'", replace
}
*1980
use "Leverans\Fob80", replace
duplicates tag dnr2018186, g(duplicate)
drop if duplicate!=0
destring lan kommun, replace
rename (dnr2018186 lan kommun)  (idnr county mun)
g earn1980=arbink*100
lab var idnr "identity number"
lab var county "county"
lab var mun "municipality"
lab var earn1980 "earnings"
keep idnr earn1980 county mun yrke syss
compress
sort idnr
save "use\fob1980", replace
*1985-1990
forval i=85(5)90{
	use "Leverans\fob`i'", replace
	duplicates tag idnr, g(duplicate)
	drop if duplicate!=0
	destring lan kommun, replace
	rename (dnr2018186 lan kommun)  (idnr county mun)
	gen earn19`i'=arbink*100
	lab var idnr "identity number"
	lab var county "county"
	lab var mun "municipality"
	lab var yob "year of birth"
	lab var earn19`i' "earnings"
	keep idnr earn19`i' county mun yrke85 yrke80 syss yrkestall
	compress
	sort idnr
	save "use\fob19`i'", replace
}
********************************************************************************
*4) Preparing the income and taxation records
*68
use "Leverans\Iot68", replace
duplicates tag dnr2018186, g(duplicate)
drop if duplicate !=0
rename (dnr2018186 dnr2018186sam) (idnr sams)
egen earn1968=rowtotal(injo inro intj sjoin), missing
lab var idnr "identity number"
lab var earn "earnings"
keep idnr earn1968 sams
compress
sort idnr
save "use\iot1968", replace
*71-82
local years "71 73 76 79 82"
foreach i of local years{
	use "Leverans\Iot`i'.dta", replace
	duplicates tag dnr2018186, g(duplicate)
	drop if duplicate!=0
	rename (dnr2018186 dnr2018186sam) (idnr sams)
	egen earn19`i'=rowtotal(ainjo ainro aintj sjoin), missing
	lab var idnr "identity number"
	lab var earn19`i' "earnings"
	keep idnr earn19`i' sams
	compress
	sort idnr
	save "use\iot19`i'", replace
}
forval i=79(3)82{
	use "use\iot19`i'", replace
	replace earn19`i'=earn19`i'*100
	save "use\iot19`i'", replace
}
********************************************************************************
*5) Preparing the LOUISE data
*1985-1989
forval i=1985(1)1989{
	use "Leverans\louisesysreggeo`i'", replace
	duplicates tag dnr2018186, g(duplicate)
	drop if duplicate!=0
	destring hutbavar hutbsun fodar, force replace
	rename (dnr2018186 fodar seninvar lan kommun) (idnr yob yim county mun)
	lab var idnr "identity number"
	lab var hutbavar "education completion"
	lab var hutbsun  "education level"
	lab var yob "year of birth"
	lab var yim "year of immigration"
	egen earn`i'=rowtotal(loneink fink arbers), missing
	lab var earn`i' "earnings"
	g year=`i'
	keep idnr yob yim county mun sams hutbsun hutbavar earn`i' year
	compress
	sort idnr
	save "use\louise`i'", replace
}
*1990
use "Leverans\louisegeo1990", replace
duplicates tag dnr2018186, g(duplicate)
drop if duplicate!=0
destring hutbavar fodar, force replace
rename (dnr2018186 dnr2018186familid fodar seninvar lan kommun) (idnr famid yob yim county mun)
lab var idnr "identity number"
lab var famid "family id"
lab var hutbavar "education completion"
lab var hutbsun  "education level"
lab var yob "year of birth"
lab var yim "year of immigration"
replace loneink=loneink*100
replace arbers=arbers*100
egen earn1990=rowtotal(loneink arbers), missing
lab var earn1990 "earnings"
g year=1990
keep idnr famid yob yim county mun sams hutbsun hutbavar earn1990 year
compress
sort idnr
save "use\louise1990", replace
*1991-1999
forval i=1991(1)1999{
	use "Leverans\louisegeo`i'", replace
	duplicates tag dnr2018186, g(duplicate)
	drop if duplicate!=0
	destring hutbavar hutbsun fodar, force replace
	rename (dnr2018186 dnr2018186familid fodar seninvar lan kommun) (idnr famid yob yim county mun)
	lab var idnr "identity number"
	lab var famid "family id"
	lab var hutbavar "education completion"
	lab var hutbsun  "education level"
	lab var yob "year of birth"
	lab var yim "year of immigration"
	replace loneink=loneink*100
	replace fink=fink*100
	replace arbers=arbers*100
	egen earn`i'=rowtotal(loneink fink arbers), missing
	lab var earn`i' "earnings"
	g year=`i'
	keep idnr famid yob yim county mun sams hutbsun hutbavar earn`i' year
	compress
	sort idnr
	save "use\louise`i'", replace
}
*2000
use "Leverans\louisegeo2000", replace
duplicates tag dnr2018186, g(duplicate)
drop if duplicate!=0
destring hutbavar sun2000niva fodar, force replace
rename (dnr2018186 dnr2018186familid fodar seninvar lan kommun) (idnr famid yob yim county mun)
lab var idnr "identity number"
lab var famid "family id"
lab var hutbavar "education completion"
lab var yob "year of birth"
lab var yim "year of immigration"
replace loneink=loneink*100
replace fink=fink*100
replace arbers=arbers*100
egen earn2000=rowtotal(loneink fink arbers), missing
lab var earn2000 "earnings"
g year=2000
keep idnr famid yob yim county mun sams hutbavar sun2000inr sun2000niva earn2000 year
compress
sort idnr
save "use\louise2000", replace
*2001-2002
forval i=2001(1)2002{
	use "Leverans\louisegeo`i'", replace
	duplicates tag dnr2018186, g(duplicate)
	drop if duplicate!=0
	destring lan kommun sun2000niva fodar, i(****) replace
	rename (dnr2018186 dnr2018186familid fodar seninvar lan kommun) (idnr famid yob yim county mun)
	lab var idnr "identity number"
	lab var famid "family id"
	lab var yob "year of birth"
	lab var yim "year of immigration"
	g earn`i'=forvers*100
	lab var earn`i' "earnings"
	g year=`i'
	keep idnr famid yob yim county mun sams sun2000inr sun2000niva earn`i' year
	compress
	sort idnr
	save "use\louise`i'", replace
}
*2003-2015
forval i=2003(1)2015{
	use "Leverans\louisegeo`i'", replace
	duplicates tag dnr2018186, g(duplicate)
	drop if duplicate!=0
	destring lan kommun sun2000niva fodar, i(****) replace
	rename (dnr2018186 dnr2018186familid fodar seninvar lan kommun) (idnr famid yob yim county mun)
	lab var idnr "identity number"
	lab var famid "family id"
	lab var yob "year of birth"
	lab var yim "year of immigration"
	g earn`i'=forvers*100
	lab var earn`i' "earnings"
	g year=`i'
	keep idnr famid yob yim county mun sams sun2000inr sun2000niva earn`i' year
	compress
	sort idnr
	save "use\louise`i'", replace
}
*2016
use "Leverans\louisegeo2016", replace
duplicates tag dnr2018186 forvers, g(duplicate)
drop if duplicate!=0
destring lan kommun sun2000niva fodar seninvar lan kommun forvers, i(****) replace
rename (dnr2018186 dnr2018186familid fodar seninvar lan kommun) (idnr famid yob yim county mun)
lab var idnr "identity number"
lab var famid "family id"
lab var yob "year of birth"
lab var yim "year of immigration"
g earn2016=forvers*100
lab var earn2016 "earnings"
g year=2016
keep idnr famid yob yim county mun sams sun2000inr sun2000niva earn2016 year
compress
sort idnr
save "use\louise2016", replace
*2017-2019
forval i=2017/2019{
	use "K1\louisegeo`i'", replace
	duplicates tag dnr2018186 forvers, g(duplicate)
	drop if duplicate!=0
	destring lan kommun fodar seninvar lan kommun forvers, i(****) replace
	rename (dnr2018186 fodar seninvar lan kommun) (idnr yob yim county mun)
	lab var idnr "identity number"
	lab var yob "year of birth"
	lab var yim "year of immigration"
	g earn`i'=forvers*100
	lab var earn`i' "earnings"
	g year=`i'
	keep idnr yob yim county mun sun20* earn`i' year
	compress
	sort idnr
	save "use\louise`i'", replace
}

********************************************************************************
*6) Preparing an education dataset
forval i=1985/1999{
	use idnr hutbavar hutbsun if hutbsun!=. using "use\louise`i'", replace
	merge 1:1 idnr using "use\birthrecords", keepusing(yob woman) keep(3) nogen
	rename (hutbavar hutbsun) (eyear`i' esun`i')
	compress
	sort idnr
	save "use\louise`i'e", replace
}
forval i=2000/2016{
	use idnr sun2000niva sun2000inr if sun2000niva!=. using "use\louise`i'", replace
	merge 1:1 idnr using "use\birthrecords", keepusing(yob woman) keep(3) nogen
	rename sun2000niva esun2000a`i'
	rename sun2000inr esun2000b`i'
	compress
	sort idnr
	save "use\louise`i'e", replace
}
*Creating a wide dataset
use idnr yob esun1985 eyear1985 using "use\louise1985e.dta", replace
save "use\edu_wide", replace
forval i=1986/1999{
	use idnr yob esun`i' eyear`i' using "use\louise`i'e.dta", replace
	merge 1:1 idnr using "use\edu_wide", nogen
	compress
	sort idnr
	save "use\edu_wide", replace
	}
forval i=2000/2016{
 	use idnr yob esun2000b`i' esun2000a`i' using "use\louise`i'e.dta", replace
	merge 1:1 idnr using "use\edu_wide", nogen
	compress
	sort idnr
	save "use\edu_wide", replace
}
*Recoding Sun2000-codes to old sun-codes
forval i=2000/2016{
	g sun2000n=esun2000a`i'
	g str4 sun2000t=esun2000b`i'
	sort sun2000n sun2000t
	merge sun2000n sun2000t using "P:\2018\186\use\sun_sun2000.dta", keep(1 3) nogen
	rename sun esun`i'
	lab var esun`i' "highest education, SUN-code"
	drop sun2000n sun2000t esun2000a`i' esun2000b`i'
}
forval i=1985/2016{
	g edlev`i'=int(esun`i'/1000)-int(esun`i'/10000)*10
	replace edlev`i'=. if (edlev`i'==0 | edlev`i'==8 | edlev`i'==9)
	lab var edlev`i' "highest educational level"	
}
compress
sort idnr
save "use\edu_wide", replace
*Creating a long dataset
use idnr edlev* yob using "use\edu_wide", replace
reshape long edlev, i(idnr) j(year)
*Filling in missing observations with latest observed education
sort idnr year
replace edlev=edlev[_n-1] if edlev==. & edlev[_n-1]!=. & idnr==idnr[_n-1] 
*Removing individuals with no educational information
drop if  edlev==.
*Creating years of education
g edu=.
replace edu=7 if edlev==1
replace edu=9 if edlev==2
replace edu=10.5 if edlev==3
replace edu=12 if edlev==4
replace edu=14 if edlev==5
replace edu=16 if edlev==6
replace edu=20 if edlev==7
*Dropping individuals under the age of 16
g age=year-yob
drop if age<16
lab var year "year"
lab var edu "years of education"
lab var edlev "highest educational level"
compress
sort idnr year
save "use\edu_long", replace
*Creating a dataset with only the highest educational level
use "use\edu_long", replace
gsort idnr -edlev
egen temp1=seq(), by(idnr)
keep if temp1==1
drop temp1 age
*Adding gender
sort idnr
merge 1:1 idnr using "use\birthrecords", keep(1 3) keepusing(woman) nogen
compress
sort idnr 
save "use\edu_max", replace